<?
include_once("/home/adlisting.jp/web/listing/Dao/db.php");
require_once("CheckHoliday.php");

//日付
$year = date("Y");
$i = $month = date("m"); 
$day = date("d"); 
$date_chk = mktime(0,0,0,$month,$day,$year);




	if($i == 12){
		$next_month = 1;
		$next_year = $year+1;
	}else{
		$next_month = $i+1;
		$next_year = $year;
	}

	$from_date = sprintf("%04d-%02d-%02d",$year,$i,1);
	$to_date = sprintf("%04d-%02d-%02d",$next_year,$next_month,1);
	




/*******************************************************************************************
	cv,sales,salesValue,clickCvSpan,postCvCntdirectCvCnt,
	indirectCvCnt,asistCvCnt,impression,clickCnt,cost,ctr,cpc,cvr,cpa,cpm,roas
	月間広告データ集計(広告グループ別)
*******************************************************************************************/

	/*
	//データ区分別 GROUP句
	$group_by[] = "GROUP BY A.adGroup_Id";
	$group_by[] = "GROUP BY A.campaign_Id";
	$group_by[] = "GROUP BY A.memberId";	

	//データ区分別 SELECT句	
	$select_word[] = "";
	*/

/*
impression
clickCnt
cost

AdReport
*/
	$sql = "SELECT 
				memberId,
				campaign_Id,
				adGroup_Id,
				SUM(impression) AS impression,
				SUM(clickCnt) AS clickCnt,
				SUM(cost) AS cost
			FROM adreport 
			WHERE date < '$to_date' AND date >= '$from_date'
			GROUP BY adGroup_Id
			";
	$result = mysql_query($sql);
	echo $sql;
	$adreport = array();
	$adgrp_id_arr = array();

	while($row = mysql_fetch_array($result)){
		array_map("checkNull",$row);	
		$adGroup_Id = $row['adGroup_Id'];
		$adreport[$adGroup_Id]['memberId'] = $row['memberId'];
		$adreport[$adGroup_Id]['campaign_Id'] = $row['campaign_Id'];
		$adreport[$adGroup_Id]['impression'] = $row['impression'];
		$adreport[$adGroup_Id]['clickCnt'] = $row['clickCnt'];
		$adreport[$adGroup_Id]['cost'] = $row['cost'];
		
		array_push($adgrp_id_arr,$adGroup_Id);
	}
print_r($adreport);

/*
cv
sales
salesValue
clickCvSpan
postCvCnt
directCvCnt
indirectCvCnt
asistCvCnt

AdCVReport
*/

	$sql = "SELECT 
				memberId,
				campaign_Id,
				adGroup_Id,
				SUM(cv) AS cv,
				SUM(sales) AS sales, 
				SUM(salesValue) AS salesValue,
				SUM(clickCvSpan)/COUNT(clickCvSpan) AS clickCvSpan,
				SUM(postCvCnt) AS postCvCnt,
				SUM(directCvCnt) AS directCvCnt,
				SUM(indirectCvCnt) AS indirectCvCnt,
				SUM(asistCvCnt) AS asistCvCnt
			FROM adcvreport
			WHERE date < '$to_date' AND date >= '$from_date'
			GROUP BY adGroup_Id
			";
	$result = mysql_query($sql);
	echo $sql;
	$adcvreport = array();
	while($row = mysql_fetch_array($result)){
		//array_map("checkNull",$row);
		$adGroup_Id = $row['adGroup_Id'];
		$adcvreport[$adGroup_Id]['memberId'] = $row['memberId'];
		$adcvreport[$adGroup_Id]['campaign_Id'] = $row['campaign_Id'];
		$adcvreport[$adGroup_Id]['cv'] = $row['cv'];
		$adcvreport[$adGroup_Id]['sales'] = $row['sales'];
		$adcvreport[$adGroup_Id]['salesValue'] = $row['salesValue'];
		$adcvreport[$adGroup_Id]['clickCvSpan'] = $row['clickCvSpan'];
		$adcvreport[$adGroup_Id]['postCvCnt'] = $row['postCvCnt'];
		$adcvreport[$adGroup_Id]['directCvCnt'] = $row['directCvCnt'];
		$adcvreport[$adGroup_Id]['indirectCvCnt'] = $row['indirectCvCnt'];
		$adcvreport[$adGroup_Id]['asistCvCnt'] = $row['asistCvCnt'];

	}

print_r($adcvreport);



for($i=0;$i<count($adgrp_id_arr);$i++){

		$adGroup_Id = $adgrp_id_arr[$i];


		$date=$from_date;
		$memberId=$adreport[$adGroup_Id]['memberId'];
		$campainId=$adreport[$adGroup_Id]['campaign_Id'];
		$adGroupId=$adGroup_Id;
		$impression=checkNull($adreport[$adGroup_Id]['impression']);
		$clickCnt=checkNull($adreport[$adGroup_Id]['clickCnt']);
		$cost= checkNull($adreport[$adGroup_Id]['cost']);


		$cv=checkNull($adcvreport[$adGroup_Id]['cv']);
		$sales=checkNull($adcvreport[$adGroup_Id]['sales']);
		$salesValue=checkNull($adcvreport[$adGroup_Id]['salesValue']);
		$clickCvSpan=checkNull($adcvreport[$adGroup_Id]['clickCvSpan']);
		$postCvCnt=checkNull($adcvreport[$adGroup_Id]['postCvCnt']);
		$directCvCnt=checkNull($adcvreport[$adGroup_Id]['directCvCnt']);
		$indirectCvCnt=checkNull($adcvreport[$adGroup_Id]['indirectCvCnt']);
		$asistCvCnt=checkNull($adcvreport[$adGroup_Id]['asistCvCnt']);


/*
	SUM(clickCnt)*100/SUM(impression) AS ctr,
	SUM(cost)/SUM(clickCnt) AS cpc,
	SUM(cv)*100/SUM(clickCnt) AS cvr,
	SUM(cost)/SUM(cv) AS cpa,
	SUM(cost)*1000/SUM(impression) AS cpm,
	SUM(cv)/SUM(cost) AS roas
*/
		$ctr= checkNull($clickCnt*100/$impression);
		$cpc= checkNull($cost/$clickCnt);
		$cvr=checkNull($cv*100/$clickCnt);
		$cpa=checkNull($cost/$cv);
		$cpm=checkNull($cost*1000/$impression);
		$roas=checkNull($cv/$cost);


	$sql = "SELECT count(*) AS cnt FROM admonthlyreport 
			WHERE memberId=$memberId AND campaign_Id=$campainId AND adGroup_Id = $adGroupId AND date='$date'";

	echo $sql;
	$result_cnt = mysql_query($sql);
	$row_cnt = mysql_fetch_array($result_cnt);
	
	if($row_cnt['cnt']==0){
		//月初
		$sql = "INSERT INTO admonthlyreport(date,memberId,campaign_Id,adGroup_Id,
							cv,sales,salesValue,clickCvSpan,postCvCnt,
							directCvCnt,indirectCvCnt,asistCvCnt,impression,
							clickCnt,cost,ctr,cpc,cvr,cpa,cpm,roas) 
							VALUES('$date',$memberId,$campainId,$adGroupId,
							$cv,$sales,$salesValue,'$clickCvSpan',$postCvCnt,
							$directCvCnt,$indirectCvCnt,$asistCvCnt,$impression,
							$clickCnt,$cost,$ctr,$cpc,$cvr,$cpa,$cpm,$roas)";

	}else{
		//月初以外
		$sql = "UPDATE admonthlyreport SET date='$date',cv=$cv,sales=$sales,
							salesValue=$salesValue,clickCvSpan='$clickCvSpan',postCvCnt=$postCvCnt,
							directCvCnt=$directCvCnt,indirectCvCnt=$indirectCvCnt,
							asistCvCnt=$asistCvCnt,impression=$impression,
							clickCnt=$clickCnt,cost=$cost,ctr=$ctr,cpc=$cpc,
							cvr=$cvr,cpa=$cpa,cpm=$cpm,roas=$roas
				WHERE memberId=$memberId AND campaign_Id=$campainId AND adGroup_Id=$adGroupId AND date='$date'";
	}

	mysql_query($sql);		
	echo $sql;
}
		

function checkNull($str){
	if($str=='') 
		return 0;
	else
		return $str;
}


?>
